package com.js.data.dtmg.util;

import cn.hutool.extra.spring.SpringUtil;
import com.alibaba.fastjson.JSONObject;
import com.js.data.common.utils.DateUtil;
import com.js.data.common.utils.StringUtils;
import com.js.data.dtmg.datasource.DataSourceConfig;
import com.js.data.dtmg.dto.DtDatasourceDto;
import com.js.data.dtmg.dto.DtTaskDto;
import com.js.data.dtmg.entity.DtTaskLog;
import com.js.data.dtmg.entity.DtDatasource;
import com.js.data.dtmg.entity.DtTask;
import com.js.data.dtmg.mapper.SqlMapper;
import com.js.data.dtmg.service.DtDatasourceService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

/**
 * @author Administrator
 */
@Slf4j
public class DbUtil {
    private static final DtDatasourceService dtDatasourceService = SpringUtil.getBean(DtDatasourceService.class);
    private static final SqlMapper sqlMapper = SpringUtil.getBean(SqlMapper.class);
    private static final JdbcTemplate jdbcTemplate = SpringUtil.getBean(JdbcTemplate.class);
    /**
     * 根据数据源ID查询数据库表
     * @param dtId 数据源ID
     * @return 数据库表
     */
    public static List<Map<String,String>> getDbTables(Long dtId){
        List<Map<String,String>> rtnList = new ArrayList<>();
        DtDatasource dtDatasource = dtDatasourceService.getById(dtId);
        DtDatasourceDto dto = new DtDatasourceDto();
        BeanUtils.copyProperties(dtDatasource,dto);
        DataSource dataSource = DataSourceConfig.getDataSource(dto);
        if(dataSource == null){
            return rtnList;
        }
        Connection conn = null;
        ResultSet resultSet = null;
        try {
            conn =  dataSource.getConnection();
            DatabaseMetaData metaData = conn.getMetaData();
            /**
             * ResultSet DatabaseMetaData.getTables(String catalog,String schemaPattern,String tableNamePattern,String[] types)
             * 功能描述：得到指定参数的表信息
             * 参数说明：
             * 参数catalog: 目录名称，一般都为空，在MySQL中代表数据库名称
             * 参数schemaPattern: 数据库名称模式匹配，null表示不缩小搜索范围数据库名，对于oracle来说就用户名
             * 参数tableNamePattern: 表名称模式匹配字符，
             * 参数types: 表类型列表，包含值(TABLE | VIEW)，null返回所有类型
             */
            resultSet = metaData.getTables(dto.getDbName(),null,null,new String[]{"TABLE"});
            while (resultSet.next()){
                Map<String,String> map = new HashMap<>();
                map.put("name",resultSet.getString("TABLE_NAME"));
                map.put("comment",resultSet.getString("REMARKS"));
                rtnList.add(map);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                if(resultSet!= null){
                    resultSet.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return rtnList;
    }

    /**
     * 根据任务信息创建ods表
     * @param taskDto 任务信息
     */
    public static void createOdsTableByTaskid(DtTaskDto taskDto){
        //获取数据源
        DtDatasource dtDatasource = dtDatasourceService.getById(taskDto.getDtId());
        DtDatasourceDto dto = new DtDatasourceDto();
        BeanUtils.copyProperties(dtDatasource,dto);
        DataSource dataSource = DataSourceConfig.getDataSource(dto);
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        try {
            conn =  dataSource.getConnection();

            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append(" create table ").append(taskDto.getOdsTableName()).append("(");

            String inSql =" select * from ("+covertSql(taskDto.getInData(),taskDto.getInParam())+") x limit 1";
            preparedStatement = conn.prepareStatement(inSql);

            ResultSetMetaData resultSetMetaData = preparedStatement.executeQuery().getMetaData();

            for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                String colType = resultSetMetaData.getColumnTypeName(i);
                int colSize = resultSetMetaData.getColumnDisplaySize(i);
                sqlBuilder.append("`").append(resultSetMetaData.getColumnName(i)).append("`")
                        .append(" ").append(colType);
                if(!"text".equalsIgnoreCase(colType) && !colType.toLowerCase().startsWith("date")){
                            sqlBuilder.append("(").append(colSize==0?255:colSize).append(")");
                }
                sqlBuilder.append(" ,");
            }
            sqlBuilder.append("in_time_no    varchar(20) not null default '' comment '批次' ")
                    .append(")  COMMENT = '").append(taskDto.getOdsTableRemarks()).append("';");
            log.info(sqlBuilder.toString());
            //先判断表中是否有数据
            boolean bCreate = true;
            try{
                String sql = " select * from " + taskDto.getOdsTableName() + "  limit 1";
                List<Map<String, Object>> list = sqlMapper.getSqlData(sql);
                if (list != null && !list.isEmpty()) {
                    //throw new ApiException("表["+taskDto.getOdsTableName()+"]已存在且存在数据");
                    bCreate = false;
                } else {
                    sqlMapper.updateSql(" drop table if exists "+taskDto.getOdsTableName());
                }
            } catch (Exception e){}
            //创建表
            if(bCreate){
                sqlMapper.updateSql(sqlBuilder.toString());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
                try {
                    if(preparedStatement != null){
                        preparedStatement.close();
                    }
                    if(conn != null){
                        conn.close();
                    }
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }


        }
    }
  public static void doTask(DtTaskLog dtTaskLog){

  }
    /**
     * 替换sql参数
     * @param sql sql
     * @param inParam 参数
     * @return 替换后的sql
     */
    public static String covertSql(String sql ,String inParam){
        if(StringUtils.isEmpty(inParam)){
            return sql;
        }
       JSONObject jsonParam = JSONObject.parseObject(replaceParam(inParam));
       Set<String> keys = jsonParam.keySet();
       for(String key:keys){
           Object objValue = jsonParam.get(key);
           if(objValue instanceof String){
               sql = sql.replaceAll("\\$\\{"+key+"\\}","'"+objValue+"'");
           }else{
               sql = sql.replaceAll("\\$\\{"+key+"\\}",String.valueOf(objValue));
           }
       }
       return sql;
    }

   public static String replaceParam(String inParam){
        return inParam.replaceAll("\\$\\{curDate\\}", DateUtil.getDate());
   }
    public static Connection getConn(DtDatasource dtDatasource) throws SQLException {
        DtDatasourceDto dto = new DtDatasourceDto();
        BeanUtils.copyProperties(dtDatasource,dto);
        DataSource dataSource = DataSourceConfig.getDataSource(dto);
        return dataSource.getConnection();
    }

    public static Map<String,Object> callProcedure(DtTask task) {
        // 调用存储过程的名称
        String proName = "doProcedureTask_"+task.getId();
        StringBuilder sqlBuilder = new StringBuilder("{ call "+proName+"(");

        String content = task.getContent();
        String contentParam = content.substring(content.indexOf("(")+1,content.indexOf("begin"));
        String param = contentParam.substring(0,contentParam.lastIndexOf(")"));
        if(StringUtils.isNotEmpty(param)){
           String[] params =  param.split(",");
           for(int i=0;i<params.length;i++){
               if(i>0){
                   sqlBuilder.append(",");
               }
               sqlBuilder.append("?");
           }
        }
        sqlBuilder.append(")}");
        // 调用存储过程的名称
        JSONObject jsonParam = JSONObject.parseObject(replaceParam(task.getInParam()));
        List<Map<String,Object>> outParamList = new ArrayList<>();
        return jdbcTemplate.execute(
                new CallableStatementCreator() {
                    @Override
                    public CallableStatement createCallableStatement(java.sql.Connection con) throws SQLException {
                        CallableStatement cs = con.prepareCall(sqlBuilder.toString());
                        if(StringUtils.isNotEmpty(param)){
                            String[] params =  param.split(",");
                            for(int i=0;i<params.length;i++){
                                String[] paramAttr = params[i].trim().replaceAll("  "," ").split(" ");
                                Map<String,Object> mapOut = new HashMap<>();
                                // 设置输入参数
                                if("in".equalsIgnoreCase(paramAttr[0])){
                                    cs.setString(i+1, jsonParam.getString(paramAttr[1]));
                                }
                                if("out".equalsIgnoreCase(paramAttr[0])){
                                    mapOut.put("no",(i+1));
                                    mapOut.put("name",paramAttr[1]);
                                    if(paramAttr[2].contains("char")){
                                        cs.registerOutParameter(i+1, Types.VARCHAR);
                                        mapOut.put("type",Types.VARCHAR);
                                    }else{
                                        cs.registerOutParameter(i+1, Types.INTEGER);
                                        mapOut.put("type",Types.INTEGER);
                                    }
                                    outParamList.add(mapOut);
                                }
                            }
                        }
                        return cs;
                    }
                },
                new CallableStatementCallback<Map<String,Object>>() {
                    @Override
                    public Map<String,Object> doInCallableStatement(CallableStatement cs) throws SQLException {
                        Map<String,Object> mapRtn = new HashMap<>();
                        cs.execute();
                        // cs.getString(2); // 获取输出参数
                        for(Map<String,Object> mapOut: outParamList){
                            if(mapOut.get("type").equals(Types.VARCHAR)){
                                mapRtn.put((String)mapOut.get("name"),cs.getString((Integer) mapOut.get("no")));
                            }else{
                                mapRtn.put((String)mapOut.get("name"),cs.getInt((Integer) mapOut.get("no")));
                            }
                        }
                        return mapRtn;
                    }
                }
        );
    }
}
